﻿using DBUtil.MetaData;
using DBUtil.Provider.SqlServer.MetaData;
using DotNetCommon;
using DotNetCommon.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DBUtil.Provider.SqlServer;

public partial class SqlServerManage : DBManage
{
    #region 生成增删改查代码
    /// <inheritdoc />
    public override async Task<string> GenerateCodeAsync(string tablePureName, string schemaPureName)
    {
        return await _generateCode(schemaPureName, tablePureName);
    }

    private async Task<string> _generateCode(string tablePureName, string schemaPureName, bool isView = false)
    {
        (schemaPureName, tablePureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, tablePureName);
        var code = "";
        SqlServerTable table = null;
        SqlServerView view = null;
        if (isView)
        {
            view = await ShowViewDetailAsync(tablePureName, schemaPureName) as SqlServerView;
        }
        else
        {
            table = await ShowTableDetailAsync(tablePureName, schemaPureName) as SqlServerTable;
        }
        var name = (isView ? view.Name : table.Name);
        code += $@"
using System;
using System.Collections.Generic;
using System.Linq;
using Newtonsoft.Json;
using DotNetCommon.Serialize; // dotnet add package DotNetCommon
public class {name}
{{
        public const string TABLE_NAME = ""{name}"";
        public const string KEY_NAME = ""{table?.PrimaryKey}"";
        private static List<string> allColumns;
        static {name}()
        {{
            allColumns = new List<string>();
            var type = typeof({name});
            var props = type.GetProperties().ToList();
            props.ForEach(prop => allColumns.Add(prop.Name));
        }}
        public static List<string> AllColumns => allColumns;
";
        var columns = isView ? view.Columns : table.Columns;
        foreach (var item in columns)
        {
            var col = item as SqlServerColumn;
            var type = "";
            switch (col.Type)
            {
                case EnumSqlServerColumnType.Bit: type = "bool"; break;

                case EnumSqlServerColumnType.TinyInt: type = "sbyte"; break;
                case EnumSqlServerColumnType.SmallInt: type = "short"; break;
                case EnumSqlServerColumnType.Int: type = "int"; break;
                case EnumSqlServerColumnType.BigInt: type = "long"; break;

                case EnumSqlServerColumnType.Float:
                case EnumSqlServerColumnType.Real:
                    type = "double";
                    break;

                case EnumSqlServerColumnType.Numeric:
                case EnumSqlServerColumnType.Decimal:
                    type = "decimal";
                    break;

                case EnumSqlServerColumnType.SmallMoney:
                case EnumSqlServerColumnType.Money:
                    type = "decimal";
                    break;

                case EnumSqlServerColumnType.Char:
                case EnumSqlServerColumnType.VarChar:
                case EnumSqlServerColumnType.NChar:
                case EnumSqlServerColumnType.NVarChar:
                case EnumSqlServerColumnType.Text:
                case EnumSqlServerColumnType.NText:
                    type = "string";
                    break;

                case EnumSqlServerColumnType.Binary:
                case EnumSqlServerColumnType.Image:
                case EnumSqlServerColumnType.VarBinary:
                    type = "byte[]";
                    break;

                case EnumSqlServerColumnType.Date:
                case EnumSqlServerColumnType.DateTime:
                case EnumSqlServerColumnType.DateTime2:
                case EnumSqlServerColumnType.DateTimeOffset:
                case EnumSqlServerColumnType.SmallDateTime:
                case EnumSqlServerColumnType.Time:
                    type = "DateTime";
                    break;

                case EnumSqlServerColumnType.TimeStamp:
                    type = "byte[]";
                    break;

                case EnumSqlServerColumnType.Geography: type = "Microsoft.SqlServer.Types.SqlGeography"; break;
                case EnumSqlServerColumnType.Geometry: type = "Microsoft.SqlServer.Types.SqlGeometry"; break;
                case EnumSqlServerColumnType.Hierarchyid: type = "Microsoft.SqlServer.Types.SqlHierarchyId"; break;
                case EnumSqlServerColumnType.UniqueIdentifier: type = "Guid"; break;
                case EnumSqlServerColumnType.SqlVariant: type = "object"; break;
                case EnumSqlServerColumnType.Xml: type = "string"; break;
                case EnumSqlServerColumnType.Other: type = "object"; break;
                default:
                    break;
            }
            if (col.IsNullAble)
            {
                if (new string[] { "object", "byte[]", "string" }.Contains(type)) { }
                else
                {
                    type += "?";
                }
            }
            var desc = col.Desc;
            desc = desc.Replace("\r\n", " ");
            if (type == "long")
            {
                code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        [JsonConverter(typeof(NumberConverter), NumberConverterShip.Int64)]
        public {type} {col.Name} {{ set; get; }}";
            }
            else
            {
                code += @$"

        /// <summary>
        /// {desc}
        /// </summary>
        public {type} {col.Name} {{ get; set; }}";
            }

        }
        code += "\r\n}";
        return code;
    }
    #endregion

    #region 生成insert语句 GenerateInsertSql/GenerateInsertSqlFile
    /// <inheritdoc />
    public override async Task<(long exportCount, string sql, long totalCount)> GenerateInsertSqlAsync(string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var objName = db.ParseQuotedName(tableQuotedName);
        var sb = new StringBuilder();
        var exportCount = await GenerateInsertSqlInternal(str =>
        {
            sb.Append(str);
            return ValueTask.CompletedTask;
        }, objName, limitCount, filter, order);
        var totalCount = await GetCountAsync(objName.FullNameQuoted, filter);
        var sql = sb.ToString();
        sb.Clear();
        return (exportCount, sql, totalCount);
    }

    /// <inheritdoc />
    public override async Task<long> GenerateInsertSqlFileAsync(string fileAbsPath, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        var dir = Path.GetDirectoryName(fileAbsPath);
        if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);

        using var fs = new FileStream(fileAbsPath, FileMode.CreateNew);
        return await GenerateInsertSqlAsync(fs, tableQuotedName, limitCount, filter, order);
    }

    /// <inheritdoc />
    public override async Task<long> GenerateInsertSqlAsync(Stream stream, string tableQuotedName, int limitCount = 0, string filter = null, string order = null)
    {
        AssertUtil.NotNullOrWhiteSpace(tableQuotedName);
        var objName = db.ParseQuotedName(tableQuotedName);
        var count = await GenerateInsertSqlInternal(async str =>
        {
            var bs = Encoding.UTF8.GetBytes(str);
            await stream.WriteAsync(bs);
        }, objName, limitCount, filter, order);
        return count;
    }

    private async Task<long> GenerateInsertSqlInternal(Func<string, ValueTask> func, TableName objName, int limitCount = 0, string filter = null, string order = null)
    {
        var table = await ShowTableDetailAsync(objName.Name, objName.SchemaName);
        var colNames = table.Columns.Select(col => db.AddQuote(col.Name)).ToList();
        await func($"print '{objName.FullNameQuoted}: 开始执行insert语句'\r\n");
        var isIdentity = table.Columns.Find(col => col.IsIdentity) != null;
        if (isIdentity)
        {
            await func($"set IDENTITY_INSERT {objName.FullNameQuoted} ON\r\n");
        }
        await func($"insert into {objName.FullNameQuoted}({string.Join(",", colNames)}) values");
        var count = 0;
        await db.SelectDataReaderAsync(async reader =>
        {
            var index = 0;
            var rowSb = new StringBuilder();
            while (await reader.RawReader.ReadAsync())
            {
                if (index > 0)
                {
                    if (index == 1)
                    {
                        //当前第二行, 把第一行的输出
                        await func("\r\n    ");
                        await func(rowSb.ToString());
                        await func(",");
                    }
                    else
                    {
                        //当前是第三行...将上一行的输出
                        await func("\r\n    ,");
                        await func(rowSb.ToString());
                        await func(",");
                    }
                    rowSb.Clear();
                }
                rowSb.Append('(');
                index++;
                var colIndex = 0;
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    var col = table.Columns[i];
                    if (col.TypeString == "timestamp") continue;
                    if (colIndex > 0) rowSb.Append(',');
                    colIndex++;
                    var value = reader.RawReader[col.Name];
                    if (value == DBNull.Value)
                    {
                        rowSb.Append("NULL");
                    }
                    else if (value is string str)
                    {
                        rowSb.Append("'" + db.EscapeString(str) + "'");
                    }
                    else if (value is byte[] bs)
                    {
                        rowSb.Append("0x").Append(bs.ToHex());
                    }
                    else if (value is DateTime || value is DateTimeOffset || value is TimeSpan)
                    {
                        rowSb.Append(db.ConvertToSqlSeg(value));
                    }
                    else if (value is Guid)
                    {
                        rowSb.Append("'" + value.ToString() + "'");
                    }
                    else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeography")
                    {
                        var prop = value.GetType().GetProperty("STSrid");
                        var grid = prop.GetValue(value);
                        rowSb.Append($"geography::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                    }
                    else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlGeometry")
                    {
                        var prop = value.GetType().GetProperty("STSrid");
                        var grid = prop.GetValue(value);
                        rowSb.Append($"geometry::STGeomFromText('{value.ToString()}', {grid.ToString()})");
                    }
                    else if (value.GetType().FullName == "Microsoft.SqlServer.Types.SqlHierarchyId")
                    {
                        rowSb.Append("'" + value.ToString() + "'");
                    }
                    else if (value is bool b)
                    {
                        rowSb.Append(b ? "1" : "0");
                    }
                    else
                    {
                        rowSb.Append(db.EscapeString(value.ToString()));
                    }
                }
                rowSb.Append(')');
                count++;
            }
            ;
            if (index == 0)
            {
                //一行也没有
                await func("no data");
            }
            else if (index == 1)
            {
                //只有一行, 收尾
                await func(rowSb.ToString());
                await func(";");
            }
            else
            {
                //有很多行, 收尾
                await func("\r\n    ");
                await func(rowSb.ToString());
                await func(";");
            }
            rowSb.Clear();
        }, $"select {(limitCount > 0 ? $"top {limitCount}" : "")} * from {objName.FullNameQuoted} {db.GenerateWhereClause(filter)} {order}");
        if (isIdentity)
        {
            await func($"\r\n\r\nset IDENTITY_INSERT {objName.FullNameQuoted} OFF\r\n");
        }

        await func($"\r\nprint '{objName.FullNameQuoted}: 共执行了{count}次insert(自行判断执行成功或失败)'");
        var total = await GetCountAsync(objName.FullNameQuoted, filter);
        return total;
    }
    #endregion

    #region 生成建表脚本
    private async Task<string> GenerateCreateTableSqlAsync(Table detail, bool includeForeignKey = true)
    {
        var schemaName = detail.SchemaName;
        var tableName = detail.Name;

        var fullQuoted = db.AddQuote(detail.SchemaName) + "." + db.AddQuote(detail.Name);
        var fullSeg = db.ProtectStringToSeg(fullQuoted);
        //生成create table
        var sql = $@"IF (OBJECT_ID({fullSeg}) is not null)
begin
    drop table {fullQuoted};
    print '{fullQuoted}: 已删除旧表';
end
GO
create table {fullQuoted}(";
        var isUnionKey = detail.PrimaryKeyColumns.Count > 1;
        foreach (var item in detail.Columns)
        {
            var col = item as SqlServerColumn;
            sql += $"\r\n    {db.AddQuote(col.Name)} {col.TypeString}";
            if (col.IsIdentity)
            {
                sql += $" identity({col.IdentityStart},{col.IdentityIncre})";
            }
            if (col.IsPrimaryKey & !isUnionKey)
            {
                //单列主键
                sql += $" primary key,";
                continue;
            }
            if (col.IsComputed)
            {
                sql += $" as {col.ComputedDefinition}";
                if (col.IsPersisted.Value) sql += " PERSISTED";
            }
            else
            {
                if (!col.IsNullAble)
                {
                    sql += $" not null";
                }
                if (col.IsUnique)
                {
                    sql += " unique";
                }
                if (col.HasDefault)
                {
                    sql += $" default({col.Default})";
                }
            }
            sql += ",";
        }
        sql = sql.TrimEnd(',');
        sql += "\r\n);";
        sql += $"\r\nprint '{fullQuoted}: 已创建新表';";
        sql += "\r\nGO";
        //生成表注释
        if (!string.IsNullOrWhiteSpace(detail.Desc))
        {
            sql += $"\r\nEXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = {db.ProtectStringToSeg(detail.Desc)} , @level0type = N'SCHEMA',@level0name = {db.ProtectStringToSeg(detail.SchemaName)}, @level1type = N'TABLE',@level1name = {db.ProtectStringToSeg(detail.Name)}, @level2type = null,@level2name = null;";
            sql += $"\r\nprint '{fullQuoted}: 已添加表说明';";
        }
        //生成字段注释
        var b = false;
        foreach (var col in detail.Columns)
        {
            if (!string.IsNullOrWhiteSpace(col.Desc))
            {
                sql += $"\r\nEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value={db.ProtectStringToSeg(col.Desc)} , @level0type=N'SCHEMA',@level0name={db.ProtectStringToSeg(detail.SchemaName)}, @level1type=N'TABLE',@level1name={db.ProtectStringToSeg(detail.Name)}, @level2type=N'COLUMN',@level2name={db.ProtectStringToSeg(col.Name)}";
                b = true;
            }
        }
        if (b)
        {
            sql += $"\r\nprint '{fullQuoted}: 已添加列说明';";
        }
        //生成约束
        b = false;
        foreach (var item in detail.Constraints)
        {
            var constraint = item as SqlServerConstraint;
            if (constraint.Type == EnumConstraintType.Check)
            {
                //检查约束
                sql += $"\r\nALTER TABLE {fullQuoted} ADD CONSTRAINT {db.AddQuote(constraint.Name)} CHECK({constraint.Definition});";
                b = true;
            }
            else if (constraint.Type == EnumConstraintType.DefaultValue)
            {
                //默认约束定义在列上
            }
            else if (constraint.Type == EnumConstraintType.ForeignKey)
            {
                if (includeForeignKey)
                {
                    //外键约束
                    var detail2 = await ShowTableDetailAsync(constraint.TableName, constraint.SchemaName);
                    sql += $"\r\nALTER TABLE {fullQuoted} ADD CONSTRAINT {db.AddQuote(constraint.Name)} FOREIGN KEY({constraint.ConstraintKeys}) REFERENCES {db.AddQuote(detail.SchemaName)}.{db.AddQuote(constraint.ReferenceTableName)}({db.AddQuote(constraint.ReferenceColumnName)}) ON DELETE {constraint.Delete_Action.Replace("_", " ")} on UPDATE {constraint.Update_Action.Replace("_", " ")};";
                    b = true;
                }
            }
            else if (constraint.Type == EnumConstraintType.PrimaryKey)
            {
                //主键约束, 联合主键写在这
                if (detail.PrimaryKeyColumns.Count > 1)
                {
                    sql += $"\r\nALTER TABLE {fullQuoted} ADD CONSTRAINT {db.AddQuote(constraint.Name)} primary key ({constraint.ConstraintKeys});";
                    b = true;
                }
            }
            else if (constraint.Type == EnumConstraintType.Unique)
            {
                //唯一约束,联合唯一写在这
                if (constraint.ConstraintKeys.Contains(","))
                {
                    sql += $"\r\nALTER TABLE {fullQuoted} ADD CONSTRAINT {db.AddQuote(constraint.Name)} UNIQUE ({constraint.ConstraintKeys});";
                    b = true;
                }
            }
        }
        if (b)
        {
            sql += $"\r\nprint '{fullQuoted}: 已完成约束';";
        }

        //生成索引
        b = false;
        foreach (var item in detail.Indexes)
        {
            var index = item as SqlServerIndex;
            //Heap类型索引不用单独建立
            if (index.IndexType == "HEAP") continue;
            if (index.IsUniqueKey || index.IsPrimaryKey)
            {
                //唯一或主键自动生成的,这里不用额外加索引
                continue;
            }
            var clust = index.IsClustered ? "CLUSTERED" : "NONCLUSTERED";
            var unique = index.IsUnique ? "UNIQUE" : "";
            sql += $"\r\ncreate {unique} {clust} INDEX {db.AddQuote(index.Name)} ON {fullQuoted}({db.AddQuote(index.ColumnName)});";
            b = true;
        }
        if (b)
        {
            sql += $"\r\nprint '{fullQuoted}: 已完成索引';";
        }

        //生成触发器
        b = false;
        foreach (var item in detail.Triggers)
        {
            var trigger = item as SqlServerTrigger;
            sql += $"\r\nGO\r\n{trigger.CreateSql}";
            b = true;
        }
        if (b)
        {
            sql += $"\r\nGO\r\nprint '{fullQuoted}: 已完成触发器';";
        }

        return sql;
    }
    /// <summary>
    /// 生成建表脚本
    /// </summary>
    /// <param name="schemaPureName">模式名</param>
    /// <param name="tablePureName">表名</param>
    /// <returns></returns>
    public override async Task<string> GenerateCreateTableSqlAsync(string tablePureName, string schemaPureName = null)
    {
        var detail = await ShowTableDetailAsync(tablePureName, schemaPureName);
        var builder = new StringBuilder();
        //先检查schema
        if (detail.SchemaName != "dbo")
        {
            //含有dbo意外的schema
            builder.Append($"--------------------------发现dbo以外的schema,先确保存在" + "\r\n");
            builder.Append($@"if((select 1 from sys.schemas where name={db.ProtectStringToSeg(detail.SchemaName)}) is null)
begin
    exec ({db.ProtectStringToSeg($"create schema {db.AddQuote(detail.SchemaName)}")});
    print '新建schema: {detail.SchemaName}';
end
GO");
            builder.Append($"\r\n------------------------------------------------------------------" + "\r\n\r\n");
        }
        var tableSql = GenerateCreateTableSqlAsync(detail);
        builder.Append(tableSql);
        return builder.ToString();
    }

    /// <summary>
    /// 生成建视图语句
    /// </summary>
    /// <param name="schemaPureName"></param>
    /// <param name="viewPureName"></param>
    /// <returns></returns>
    public async Task<string> GenerateCreateViewSql(string viewPureName, string schemaPureName = null)
    {
        (schemaPureName, viewPureName, string fullQuoteName, string fullNameForSeg) = await FixTableOrViewName(schemaPureName, viewPureName);
        var detail = (await ShowViewDetailAsync(viewPureName, schemaPureName)) as SqlServerView;
        var sqlbuilder = new StringBuilder();
        sqlbuilder.AppendLine($@"
IF (OBJECT_ID({fullNameForSeg}) is not null)
begin
    drop view {fullQuoteName};
    print '{fullQuoteName}: 已删除旧视图';
end
GO
");
        sqlbuilder.AppendLine(detail.CreateSql.Trim());
        sqlbuilder.AppendLine($@"
GO
print '{fullQuoteName}: 已创建新视图';
GO");
        if (detail.Desc.IsNotNullOrWhiteSpace())
        {
            sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = {db.ProtectStringToSeg(detail.Desc)} , @level0type = N'SCHEMA',@level0name = {db.ProtectStringToSeg(detail.SchemaName)}, @level1type = N'view',@level1name = {db.ProtectStringToSeg(detail.Name)}, @level2type = null,@level2name = null;");
            sqlbuilder.AppendLine($"print '{fullQuoteName}: 已添加视图说明';");
        }
        var b = false;
        detail.Columns.ForEach(column =>
        {
            if (column.Desc.IsNotNullOrWhiteSpace())
            {
                sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value={db.ProtectStringToSeg(column.Desc)} , @level0type=N'SCHEMA',@level0name={db.ProtectStringToSeg(detail.SchemaName)}', @level1type=N'view',@level1name={db.ProtectStringToSeg(detail.Name)}, @level2type=N'COLUMN',@level2name={db.ProtectStringToSeg(column.Name)}");
                b = true;
            }
        });
        if (b)
        {
            sqlbuilder.AppendLine($"print '{fullQuoteName}: 已添加列说明';");
        }
        return sqlbuilder.ToString();

    }

    /// <summary>
    /// 生成建序列语句
    /// </summary>
    /// <param name="schemaPureName"></param>
    /// <param name="seqPureName"></param>
    /// <returns></returns>
    public async Task<string> GenerateCreateSeqSql(string seqPureName, string schemaPureName = null)
    {
        var detail = (await ShowSequencesAsync(schemaPureName)).FirstOrDefault(i => i.Name == seqPureName) as SqlServerSequence;
        var sqlbuilder = new StringBuilder();
        sqlbuilder.AppendLine($@"
IF (OBJECT_ID('[{detail.SchemaName}].[{detail.Name}]') is not null)
begin
    drop SEQUENCE [{detail.SchemaName}].{detail.Name};
    print '[{detail.SchemaName}].[{detail.Name}]: 已删除旧序列';
end
GO
");
        sqlbuilder.AppendLine(detail.CreateSql.Trim());
        sqlbuilder.AppendLine($@"
GO
print '[{detail.SchemaName}].[{detail.Name}]: 已创建新序列';
GO");
        return sqlbuilder.ToString();

    }

    /// <summary>
    /// 生成建存储过程语句
    /// </summary>
    /// <param name="schemaPureName"></param>
    /// <param name="procPureName"></param>
    /// <returns></returns>
    public async Task<string> GenerateCreateProcSql(string procPureName, string schemaPureName = null)
    {
        var detail = (await ShowProceduresAsync(schemaPureName)).FirstOrDefault(i => i.Name == procPureName) as SqlServerProcedure;
        if (detail == null) return null;
        var fullQuoted = detail.SchemaName.IsNotNullOrWhiteSpace() ? $"{db.AddQuote(detail.SchemaName)}.{db.AddQuote(detail.Name)}" : db.AddQuote(detail.Name);
        var fullSeg = db.ProtectStringToSeg(fullQuoted);
        var sqlbuilder = new StringBuilder();
        sqlbuilder.AppendLine($@"
IF (OBJECT_ID({fullSeg}) is not null)
begin
    drop proc {fullQuoted};
    print '{fullQuoted}: 已删除旧存储过程';
end
GO");
        sqlbuilder.AppendLine(detail.CreateSql.Trim());
        sqlbuilder.AppendLine($@"GO
print '{fullQuoted}: 已创建新存储过程';
GO");
        if (detail.Desc.IsNotNullOrWhiteSpace())
        {
            sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = {db.ProtectStringToSeg(detail.Desc)} , @level0type = N'SCHEMA',@level0name = {db.ProtectStringToSeg(detail.SchemaName)}, @level1type = N'PROCEDURE',@level1name = {db.ProtectStringToSeg(detail.Name)}, @level2type = null,@level2name = null;");
            sqlbuilder.AppendLine($"print '{fullQuoted}: 已添加存储过程说明';");
        }
        return sqlbuilder.ToString();
    }

    /// <summary>
    /// 生成建函数语句
    /// </summary>
    /// <param name="schemaPureName"></param>
    /// <param name="funcPureName"></param>
    /// <returns></returns>
    public async Task<string> GenerateCreateFuncSql(string funcPureName, string schemaPureName = null)
    {
        var detail = (await ShowFunctionsAsync(schemaPureName)).FirstOrDefault(i => i.Name == funcPureName) as SqlServerFunction;
        if (detail == null) return null;
        var fullQuoted = detail.SchemaName.IsNotNullOrWhiteSpace() ? $"{db.AddQuote(detail.SchemaName)}.{db.AddQuote(detail.Name)}" : db.AddQuote(detail.Name);
        var fullSeg = db.ProtectStringToSeg(fullQuoted);
        var sqlbuilder = new StringBuilder();
        sqlbuilder.AppendLine($@"
IF (OBJECT_ID({fullSeg}) is not null)
begin
    drop function {fullQuoted};
    print '{fullQuoted}: 已删除旧函数';
end
GO");
        sqlbuilder.AppendLine(detail.CreateSql.Trim());
        sqlbuilder.AppendLine($@"GO
print '{fullQuoted}: 已创建新函数';
GO");
        if (detail.Desc.IsNotNullOrWhiteSpace())
        {
            sqlbuilder.AppendLine($@"EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = {db.ProtectStringToSeg(detail.Desc)} , @level0type = N'SCHEMA',@level0name = {db.ProtectStringToSeg(detail.SchemaName)}, @level1type = N'FUNCTION',@level1name = {db.ProtectStringToSeg(detail.Name)}, @level2type = null,@level2name = null;");
            sqlbuilder.AppendLine($"print '{fullQuoted}: 已添加函数说明';");
        }
        return sqlbuilder.ToString();
    }

    /// <inheritdoc />
    public override async Task GenerateCreateTableSqlBatchAsync(Stream stream, IEnumerable<string> tableQuotedNames, bool includeInsertSql = false)
    {
        //生成脚本
        var counter = 0;
        await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 开始建表: 共 {counter} 个---------------------\r\n"));
        foreach (var table in tableQuotedNames)
        {
            counter++;
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- start 表: {table}\r\n"));
            await stream.WriteAsync(Encoding.UTF8.GetBytes(await GenerateCreateTableSqlAsync(table)));
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- end 表: {table}\r\n"));
        }
        await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 建表结束, 共: {counter} 个---------------------\r\n"));

        //插入数据
        if (includeInsertSql)
        {
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 开始插入数据---------------------\r\n"));
            foreach (var table in tableQuotedNames)
            {
                await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- start 表: {table}\r\n"));
                await GenerateInsertSqlAsync(stream, table);
                await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- end 表: {table}\r\n"));
            }
            await stream.WriteAsync(Encoding.UTF8.GetBytes($"#-------------------------- 插入结束---------------------\r\n"));
        }
    }
    #endregion
}
